package com.demo2do.core.persistence;

import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import com.demo2do.core.persistence.dialect.Demo2doJdbcDialect;
import com.demo2do.core.persistence.dialect.Dialect;

/**
 * 
 * @author Downpour
 */
public class GenericJdbcSupport {
    
    private static final Dialect DEFAULT_DIALECT = new Demo2doJdbcDialect();
    
    private NamedParameterJdbcTemplate jdbcTemplate;
    
    private Dialect dialect = DEFAULT_DIALECT;
    
    /**
     * @param dataSource
     *            the dataSource to set
     */
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }
    
    /**
     * @param dialect the dialect to set
     */
    public void setDialect(Dialect dialect) {
        this.dialect = dialect;
    }
    
    /**
     * 
     * @return
     */
    protected NamedParameterJdbcTemplate getJdbcTemplate() {
        return this.jdbcTemplate;
    }
    
    
    /**
     * Get limited SQL Sentence
     * 
     * @param dialect
     * @param sentence
     * @param beginIndex
     * @param maxResult
     * @param putValue
     * @return
     */
    protected String getLimitedSentence(String sentence, int beginIndex, int maxResult, boolean putValue) {
        return this.dialect.getSearchLimitString(sentence, beginIndex, maxResult, putValue);
    }
    
    /**
     * Query by JDBC for int result, with no parameters
     * 
     * @param sentence
     * @param parameters
     * @return
     */
    @SuppressWarnings("unchecked")
    public int queryForInt(String sentence) {
        Number number = getJdbcTemplate().queryForObject(sentence, Collections.EMPTY_MAP, Integer.class);
        return (number != null ? number.intValue() : 0);
    }
    
    /**
     * Query by JDBC for int result, using Map as basic parameter.
     * 
     * @param sentence
     * @param parameters
     * @return
     */
    public int queryForInt(String sentence, Map<String, Object> parameters) {
        Number number = getJdbcTemplate().queryForObject(sentence, parameters, Integer.class);
        return (number != null ? number.intValue() : 0);
    }

    /**
     * Query by JDBC for int result, using single parameter.
     * 
     * @param sentence
     * @param parameters
     * @return
     */
    @SuppressWarnings("serial")
    public int queryForInt(String sentence, final String key, final Object parameter) {
        Number number = getJdbcTemplate().queryForObject(sentence, new HashMap<String, Object>(){{put(key, parameter);}}, Integer.class);
        return (number != null ? number.intValue() : 0);
    }

    
    /**
     * Query by JDBC for int result, using a typical JavaBean as basic
     * parameters.
     * 
     * @param sentence
     * @param properties
     * @return
     */
    public int queryForInt(String sentence, Object properties) {
        SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(properties);
        Number number = getJdbcTemplate().queryForObject(sentence, namedParameters, Integer.class);
        return (number != null ? number.intValue() : 0);
    }

    /**
     * Query by JDBC for List result, with no parameters.
     * 
     * @param sentence
     * @param parameters
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Map<String, Object>> queryForList(String sentence) {
        return getJdbcTemplate().queryForList(sentence, Collections.EMPTY_MAP);
    }
    
    /**
     * Query by JDBC for List result, using Map as basic parameter.
     * 
     * @param sentence
     * @param parameters
     * @return
     */
    public List<Map<String, Object>> queryForList(String sentence, Map<String, Object> parameters) {
        return getJdbcTemplate().queryForList(sentence, parameters);
    }
    
    /**
     * Query by JDBC for List result, using a typical JavaBean as basic
     * parameters.
     * 
     * @param sentence
     * @param properties
     * @return
     */
    public List<Map<String, Object>> queryForList(String sentence, Object properties) {
        SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(properties);
        return getJdbcTemplate().queryForList(sentence, namedParameters);
    }

    /**
     * Query by JDBC for List result, using single parameter.
     * 
     * @param sentence
     * @param parameters
     * @return
     */
    @SuppressWarnings("serial")
    public List<Map<String, Object>> queryForList(String sentence, final String key, final Object parameter) {
        return getJdbcTemplate().queryForList(sentence, new HashMap<String, Object>(){{put(key, parameter);}});
    }
    
    /**
     * 
     * @param sentence
     * @param parameters
     * @param beginIndex
     * @param maxResult
     * @return
     */
    public List<Map<String, Object>> queryForList(String sentence, final int beginIndex, final int maxResult) {
        return this.queryForList(sentence, new HashMap<String, Object>(), beginIndex, maxResult);
    }
    
    /**
     * 
     * @param sentence
     * @param parameters
     * @param beginIndex
     * @param maxResult
     * @return
     */
    public List<Map<String, Object>> queryForList(String sentence, Map<String, Object> parameters, final int beginIndex, final int maxResult) {
        parameters.put("beginIndex", beginIndex);
        parameters.put("maxResult", maxResult);
        return getJdbcTemplate().queryForList(this.getLimitedSentence(sentence, beginIndex, maxResult, false), parameters);
    }
    
    /**
     * 
     * @param sentence
     * @param key
     * @param parameter
     * @param beginIndex
     * @param maxResult
     * @return
     */
    public List<Map<String, Object>> queryForList(String sentence, final String key, final Object parameter, final int beginIndex, final int maxResult) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put(key, parameter);
        parameters.put("beginIndex", beginIndex);
        parameters.put("maxResult", maxResult);
        return getJdbcTemplate().queryForList(this.getLimitedSentence(sentence, beginIndex, maxResult, false), parameters);
    }
    
    /**
     * Query by JDBC for list of resultClass (single column result) as result, using Map as basic parameter,
     * 
     * @param <T>
     * @param sentence
     * @param parameters
     * @param resultClass
     * @return
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public <T> List<T> queryForSingleColumnList(String sentence, Map<String, Object> parameters, Class<T> resultClass) {
        return getJdbcTemplate().query(sentence, parameters, new SingleColumnRowMapper(resultClass));
    }
    

    /**
     * Query by JDBC for list of resultClass (single column result) as result, using single parameter
     * 
     * @param <T>
     * @param sentence
     * @param parameters
     * @param resultClass
     * @return
     */
    @SuppressWarnings({ "serial", "unchecked", "rawtypes" })
    public <T> List<T> queryForSingleColumnList(String sentence, final String key, final Object parameter, Class<T> resultClass) {
        return getJdbcTemplate().query(sentence, new HashMap<String, Object>(){{put(key, parameter);}}, new SingleColumnRowMapper(resultClass));
    }

    /**
     * Query by JDBC for list of resultClass as result, using Map as basic parameter,
     * 
     * @param <T>
     * @param sentence
     * @param parameters
     * @param resultClass
     * @return
     */
    @SuppressWarnings("unchecked")
    public <T> List<T> queryForList(String sentence, Class<T> resultClass) {
        return getJdbcTemplate().query(sentence, Collections.EMPTY_MAP, BeanPropertyRowMapper.newInstance(resultClass));
    }
    
    /**
     * Query by JDBC for list of resultClass as result, using Map as basic parameter,
     * 
     * @param <T>
     * @param sentence
     * @param parameters
     * @param resultClass
     * @return
     */
    public <T> List<T> queryForList(String sentence, Map<String, Object> parameters, Class<T> resultClass) {
        return getJdbcTemplate().query(sentence, parameters, BeanPropertyRowMapper.newInstance(resultClass));
    }
    
    /**
     * Query by JDBC for list of resultClass as result, using single parameter
     * 
     * @param <T>
     * @param sentence
     * @param parameters
     * @param resultClass
     * @return
     */
    @SuppressWarnings("serial")
    public <T> List<T> queryForList(String sentence, final String key, final Object parameter, Class<T> resultClass) {
        return getJdbcTemplate().query(sentence, new HashMap<String, Object>(){{put(key, parameter);}}, BeanPropertyRowMapper.newInstance(resultClass));
    }
    
    /**
     * Query by JDBC for list result, using a typical JavaBean as parameter.
     * 
     * @param <T>
     * @param sentence
     * @param properties
     * @param resultClass
     * @return
     */
    public <T> List<T> queryForList(String sentence, Object properties, Class<T> resultClass) {
        SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(properties);
        return getJdbcTemplate().query(sentence, namedParameters, BeanPropertyRowMapper.newInstance(resultClass));
    }
    
    /**
     * 
     * @param sentence
     * @param parameters
     * @param resultClass
     * @param beginIndex
     * @param maxResult
     * @return
     */
    public <T> List<T> queryForList(String sentence, Map<String, Object> parameters, Class<T> resultClass, final int beginIndex, final int maxResult) {
        parameters.put("beginIndex", beginIndex);
        parameters.put("maxResult", maxResult);
        return this.queryForList(this.getLimitedSentence(sentence, beginIndex, maxResult, false), parameters, resultClass);
    }
    
    /**
     * 
     * @param sentence
     * @param parameters
     * @param resultClass
     * @param beginIndex
     * @param maxResult
     * @return
     */
    public <T> List<T> queryForList(String sentence, final String key, final Object parameter, Class<T> resultClass, final int beginIndex, final int maxResult) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("beginIndex", beginIndex);
        parameters.put("maxResult", maxResult);
        return this.queryForList(this.getLimitedSentence(sentence, beginIndex, maxResult, false), parameters, resultClass);
    }
    
    /**
     * Execute SQL according to parameters
     * 
     * @param sql
     * @param parameters
     */
    @SuppressWarnings("serial")
    public void executeSQL(String sql, final String key, final Object parameter) {
        getJdbcTemplate().update(sql, new HashMap<String, Object>(){{put(key, parameter);}});
    }
    
    /**
     * Execute SQL according to parameters
     * 
     * @param sql
     * @param parameters
     */
    public void executeSQL(String sql, Map<String, Object> parameters) {
        getJdbcTemplate().update(sql, parameters);
    }
    
    /**
     * Execute SQL according to bean
     * 
     * @param sql
     * @param bean
     */
    public void executeSQL(String sql, Object bean) {
        getJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(bean));
    }

}
